package com.zjy.dao.user;

import com.mysql.cj.util.StringUtils;
import com.zjy.dao.BaseDao;
import com.zjy.pojo.Role;
import com.zjy.pojo.User;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDaoImpl implements UserDao{
    //获取用户信息
    public User getLoginUser(Connection connection, String userCode) throws SQLException {
        PreparedStatement pstm=null;
        ResultSet rs=null;
        User user=null;
        if(connection!=null){
            String sql="select * from smbms_user where userCode=?";
            Object[] params={userCode};
            rs= BaseDao.execute(connection,pstm,rs,sql,params);
            //因为只返回一个rs所以用的是if
            if(rs.next()){
                user=new User();
                user.setId(rs.getInt("id"));
                System.out.println("user.getId()"+user.getId());
                user.setUserCode(rs.getString("userCode"));
                user.setUserName(rs.getString("userName"));
                user.setUserPassword(rs.getString("userPassword"));
                user.setGender(rs.getInt("gender"));
                user.setBirthday(rs.getDate("birthday"));
                user.setPhone(rs.getString("phone"));
                user.setAddress(rs.getString("address"));
                user.setUserRole(rs.getInt("userRole"));
                user.setCreatedBy(rs.getInt("createdBy"));
                user.setCreationDate(rs.getDate("creationDate"));
                user.setModifyBy(rs.getInt("modifyBy"));
                user.setModifyDate(rs.getDate("modifyDate"));

            }
            //因为事务，所以连接先不释放！
            BaseDao.closeResource(null,pstm,rs);
        }
        return user;
    }
    //修改用户密码
    public int updatePwd(Connection connection, int id, String password) throws SQLException {
        PreparedStatement pstm=null;
        int execute = 0;
        if(connection!=null){
            Object[] params={password,id};
            String sql="update smbms.smbms_user set userPassword = ? where id = ?";
            execute = BaseDao.execute(connection, pstm, sql, params);
        }
        BaseDao.closeResource(null,pstm,null);
        return execute;
    }
    //根据用户名或者角色查询用户总数【重点，最难理解】
    public int getUserCount(Connection connection, String username, int userRole) throws SQLException {
        PreparedStatement pstm=null;
        ResultSet rs=null;
        int count=0;
        if(connection!=null){
            StringBuffer sql=new StringBuffer();
            sql.append("select count(1) as count from smbms.smbms_user u,smbms.smbms_role r where u.userRole=r.id");
            ArrayList<Object> list = new ArrayList<Object>();//用来存放参数

            //sql拼接,注意前面空格
            if(!StringUtils.isNullOrEmpty(username)){
                sql.append(" and u.userName = ?");
                //模糊查询
                list.add("%"+username+"%");//index:0
            }
            if(userRole>0){
                sql.append(" and u.userRole = ?");
                list.add(userRole);//index:1
            }

            //把List转换为数组
            Object[] params = list.toArray();
            rs = BaseDao.execute(connection, pstm, rs, sql.toString(), params);
            if(rs.next()){
                //从结果集中获取最终的数量
                count = rs.getInt("count");
            }

            BaseDao.closeResource(null,pstm,rs);
        }
        return count;
    }
    //获取用户列表分页显示
    public List<User> getUserList(Connection connection, String username, int userRole, int currentPageNo, int pageSize) throws SQLException {
        PreparedStatement pstm=null;
        ResultSet rs=null;
        List<User> userlist=new ArrayList<User>();
        User _user=null;
        if(connection!=null){
            StringBuffer sql=new StringBuffer();
            sql.append("select u.*,r.roleName as userRoleName from smbms.smbms_user u,smbms.smbms_role r where u.userRole=r.id");
            List<Object> list = new ArrayList<Object>();
            if(!StringUtils.isNullOrEmpty(username)){
                sql.append(" and u.userName like ?");
                //模糊查询
                list.add("%"+username+"%");
                //System.out.println(username);
            }
            if(userRole>0){
                sql.append(" and u.userRole = ?");
                list.add(userRole);
            }
            //分页显示排序
            sql.append(" order by creationDate DESC limit ?,?");
            currentPageNo=(currentPageNo-1)*pageSize;
            list.add(currentPageNo);
            list.add(pageSize);

            Object[] params=list.toArray();
            System.out.println("sql----->"+sql.toString());
            rs=BaseDao.execute(connection,pstm,rs,sql.toString(),params);

            while (rs.next()){
                _user=new User();
                _user.setId(rs.getInt("id"));
                _user.setUserCode(rs.getString("userCode"));
                _user.setUserName(rs.getString("userName"));
                _user.setGender(rs.getInt("gender"));
                _user.setBirthday(rs.getDate("birthday"));
                _user.setPhone(rs.getString("phone"));
                _user.setUserRole(rs.getInt("userRole"));
                _user.setUserRoleName(rs.getString("userRoleName"));
                userlist.add(_user);
            }

            BaseDao.closeResource(null,pstm,rs);
        }
        return userlist;
    }
    //添加用户
    public int add(Connection connection, User user) throws SQLException {
        PreparedStatement pstm=null;
        int addRows=0;
        if(connection!=null){
            String sql="insert into smbms_user(userCode, userName, userPassword, " +
                    "gender, birthday, phone, address, userRole, createdBy, creationDate)" +
                    "values (?,?,?,?,?,?,?,?,?,?)";
            Object[] params={user.getUserCode(),user.getUserName(),user.getUserPassword(),
            user.getGender(),user.getBirthday(),user.getPhone(),user.getAddress(),
            user.getUserRole(),user.getCreatedBy(),user.getCreationDate()};

            addRows=BaseDao.execute(connection,pstm,sql,params);

            BaseDao.closeResource(null,pstm,null);
        }

        return addRows;
    }
    //删除用户
    public int delUser(Connection connection, Integer delId) throws SQLException {
        PreparedStatement ptsm=null;
        int delrow=0;
        if(connection!=null){
            String sql="delete from smbms_user where id = ?";
            Object[] params={delId};
            delrow=BaseDao.execute(connection,ptsm,sql,params);
            BaseDao.closeResource(null,ptsm,null);
            System.out.println("UserDaoImpl---->delete：" + sql);

        }
        return delrow;
    }

}
